package cn.ctyun.ipush.utils;

import cn.ctyun.ipush.model.ExcelModel;
import cn.ctyun.ipush.model.Person;
import com.aspose.cells.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.File;
import java.io.InputStream;
import java.net.URL;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: user
 * Date: 16-6-8
 * Time: 下午2:16
 * To change this template use File | Settings | File Templates.
 */
public class ExcelUtils {

    /* 日志对象 */
    private static final Logger LOG = LoggerFactory.getLogger(ExcelUtils.class);

//    /**
//     * 设置HTTP响应头
//     *
//     * @param response
//     * @param fileName
//     * @param formatSuffix
//     */
//    protected void setResponseHeader(HttpServletResponse response, String fileName, String formatSuffix) {
//        String contentType = "application/vnd.ms-excel";
//        formatSuffix = formatSuffix.toLowerCase();
//        if (formatSuffix.endsWith(".xlsx")
//                || formatSuffix.endsWith(".xlsb")
//                || formatSuffix.endsWith(".xlsm")
//                || formatSuffix.endsWith(".xltm")
//                || formatSuffix.endsWith(".xltx")) {
//            contentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//        }
//        response.setContentType(contentType);
//        response.addHeader("content-disposition", "inline;filename=" + fileName);
//    }
//
//    /**
//     * 向客户端返回excel
//     *
//     * @param request
//     * @param response
//     * @param wb
//     * @throws Exception
//     */
//    protected void sendReport(HttpServletRequest request,
//                              HttpServletResponse response, Workbook wb) throws Exception {
//        wb.save(response.getOutputStream(), wb.getFileFormat());
//    }
//
//
//    //存放Excel模板的位置
//    private static final String TEMPLATE_FILE_PATH_PART = "/Designer/SmartMarkerDesigner.xls";
//
//    /**
//     * 对客户端开发的方法
//     */
//    @RequestMapping(params = "method=getReport")
//    public Object getReport(HttpServletRequest request,
//                            HttpServletResponse response) {
//        try {
//            //创建工作薄
//            Workbook wb = createReport(request);
//            //设置输出响应头
//            setResponseHeader(response, "SmartMarker.xls", ".xlsx");
//            //向客户端输出
//            sendReport(request, response, wb);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        return null;
//    }
//
//    /**
//     * 向自定义模板中填充数据
//     */
//    protected Workbook createReport(HttpServletRequest request) throws Exception {
//
//        ServletContext sc = request.getSession().getServletContext();
//        String template_file_path = sc.getRealPath(TEMPLATE_FILE_PATH_PART);
//
//        //创建工作薄加载模板(SmartMarkerDesigner.xls)
//        Workbook wb = new Workbook(template_file_path);
//        createSmart(wb);
//
//        return wb;
//    }
//
//    private static void createSmart(Workbook wb) throws Exception {
//        WorkbookDesigner designer = new WorkbookDesigner();
//        designer.setWorkbook(wb);
//        ResultSet rs = DBUtils.getOracleConnection("jdbc:oracle:thin:@localhost:1521:ORCL", "demo", "123", "select * from pushtest");
//        designer.setDataSource("Person", rs);
//        //这个框架默认不支持HashMap(查了好多资料都没找到直接支持的方法)
//        //所以我实现了ICellsDataTable接口 使其可以支持HashMap
//        //designer.setDataSource("Map", new HashMapDataTable(InitData.getHashMap()));
//        designer.process(true);
//    }

    /**
     * 数据库表导入到excel表中,保存到指定位置
     */
    public static void dbDataToExcel(ResultSet rs, String templateExcelFilePath) {
        // 验证License
        if (!getLicense()) {
            return;
        }
        Workbook wb = null;
        WorkbookDesigner designer = new WorkbookDesigner();
        try {
            wb = new Workbook(templateExcelFilePath);
            designer.setWorkbook(wb);
            designer.setDataSource("table1", rs);
            //designer.setDataSource("Person",getPersons());
            designer.process();
            String newFileName = getExcelOutPath() + System.currentTimeMillis() + ".xlsx";
            designer.getWorkbook().save(newFileName, SaveFormat.XLSX);
        } catch (Exception e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }
    }

    /**
     * 多个数据表，放入到一个Excel模板里
     */
    public static WorkbookDesigner dbDataToExcelWithMulTables(Map<String, ResultSet> map, String templateExcelFilePath, String jobNameDayPath,ExcelModel excelModel) {
        // 验证License
        if (!getLicense()) {
            return null;
        }
        Workbook wb = null;
        WorkbookDesigner designer = new WorkbookDesigner();
        try {
            wb = new Workbook(templateExcelFilePath);
            designer.setWorkbook(wb);
            for (Map.Entry<String, ResultSet> rs : map.entrySet()) {
                designer.setDataSource(rs.getKey(), rs.getValue());
            }
            //designer.setDataSource("Person",getPersons());
            designer.process();
            designer.getWorkbook().calculateFormula();
            //v2.0 生成文件的命名规范：用户ID_时间戳_原文件名
            String newFileName = jobNameDayPath + "/" + DateUtils.timestampToDataString(System.currentTimeMillis()) + "_"+templateExcelFilePath.split("\\\\")[3].split("_")[2];
            designer.getWorkbook().save(newFileName, SaveFormat.XLSX);
            excelModel.setExcelPath(newFileName);
            return designer;
        } catch (Exception e) {
            LOG.info("数据灌入Excel失败:"+e.getMessage());  //To change body of catch statement use File | Settings | File Templates.
            return null;
        }
    }

    /**
     * test
     *
     * @return
     */
    public static List<Person> getPersons() {
        List<Person> persons = new ArrayList<Person>();
        persons.add(new Person("张三", new Date(), 25, 4022.34));
        persons.add(new Person("李四", new Date(), 56, 3580.3));
        persons.add(new Person("王宇", new Date(), 45, 5454.34));
        persons.add(new Person("小强", new Date(), 57, 5876.24));
        persons.add(new Person("微微", new Date(), 54, 4022.68));
        persons.add(new Person("丽丽", new Date(), 12, 5878.12));
        persons.add(new Person("张娜", new Date(), 37, 5454.00));
        persons.add(new Person("王华", new Date(), 60, 2221));
        persons.add(new Person("阿超", new Date(), 55, 4587));
        persons.add(new Person("黑子", new Date(), 22, 9788));
        persons.add(new Person("小王", new Date(), 37, 2212.24));
        persons.add(new Person("剌剌", new Date(), 27, 8785.24));
        persons.add(new Person("荷花", new Date(), 18, 5454.34));
        return persons;
    }

    /**
     * 得到Excel输出文件的目录
     *
     * @return
     */
    public static String getExcelOutPath() {
        try {
            //URL url = Thread.currentThread().getContextClassLoader().getResource("excel/outfiles/");
            //return url.getPath();
            return ConfigUtils.getStringValue("fileAddress");
        } catch (Exception e) {
            LOG.info("excel outfiles address not found  ");
            return null;
        }

    }

    private static InputStream license;

    /**
     * 获取license
     *
     * @return
     */
    public static boolean getLicense() {
        boolean result = false;
        try {
            //license = ExcelUtils.class.getClassLoader().getResourceAsStream("\\license.xml");    // license路径
            license = Thread.currentThread().getContextClassLoader().getResourceAsStream("license.xml");
            // 原始excel路径
            License aposeLic = new License();
            aposeLic.setLicense(license);
            result = true;
        } catch (Exception e) {
            e.printStackTrace();
        }
        return result;
    }


    /**
     * eccel  to  picture
     */
    public static void excelToPicture() {

        //get workbook path
        URL url = Thread.currentThread().getContextClassLoader().getResource("excel/outfiles/1465475641483.xlsx");
        // Creating an Workbook object with an Excel file path
        Workbook workbook = null;
        try {
            workbook = new Workbook(url.getPath());
        } catch (Exception e) {
            e.printStackTrace();  //To change body of catch statement use File | Settings | File Templates.
        }

        int cnt = workbook.getWorksheets().getCount();
        System.out.println("Page Count: " + String.valueOf(cnt));

        // Create an object for ImageOptions
        ImageOrPrintOptions imgOptions = new ImageOrPrintOptions();
        // Set the format type of the image
        imgOptions.setImageFormat(ImageFormat.getJpeg());
        imgOptions.setOnePagePerSheet(true);
        imgOptions.setHorizontalResolution(100);
        imgOptions.setVerticalResolution(100);


        //**********Add this line************//
        CellsHelper.setFontDir("c:\\windows\\fonts");

        for (int i = 0; i < workbook.getWorksheets().getCount(); i++) {
            // Get the worksheet.
            Worksheet sheet = workbook.getWorksheets().get(i);
            sheet.getPageSetup().setLeftMargin(0.0);
            sheet.getPageSetup().setRightMargin(0.0);
            sheet.getPageSetup().setBottomMargin(0.0);
            sheet.getPageSetup().setTopMargin(0.0);

            // Create a SheetRender object with respect to your desired sheet
            SheetRender sr = null;
            try {
                sr = new SheetRender(sheet, imgOptions);
            } catch (Exception e) {
                e.printStackTrace();
            }
            for (int j = 0; j < sr.getPageCount(); j++) {
                // Generate image(s) for the worksheet
                try {
                    sr.toImage(j, "D:\\2016qx\\spring-quartz-demo\\target\\classes\\excel\\outfiles\\book1_sheet_" + i + "_page_" + j + ".jpg");
                } catch (Exception e) {
                    e.printStackTrace();
                }
            }
        }
    }

    /**
     * 创建生成Excel文件夹的目录
     *
     * @param groupName
     * @param jobName
     * @return groupname/jobname/2016/07/01
     */
    public static String createFileFolderByGroupAndJobName(String groupName, String jobName) {
        String excelBasePath = getExcelOutPath()+"/"; // outfiles/
        String groupNamePath = excelBasePath + groupName;
        File groupFolder = new File(groupNamePath);
        if (!groupFolder.exists() && !groupFolder.isDirectory()) {
            groupFolder.mkdirs();
        }
        String jobNamePath = excelBasePath + groupName + "/" + jobName;
        File jobFolder = new File(jobNamePath);
        if (!jobFolder.exists() && !jobFolder.isDirectory()) {
            jobFolder.mkdirs();
        }

        String year = String.valueOf(DateTime.getNow().getYear());
        String month = String.valueOf(DateTime.getNow().getMonth());
        String day = String.valueOf(DateTime.getNow().getDay());

        String yearPath = jobNamePath + "/" + year;
        File yearFolder = new File(yearPath);
        if (!yearFolder.exists() && !yearFolder.isDirectory()) {
            yearFolder.mkdirs();
        }

        String monthPath = yearPath + "/" + month;
        File monthFolder = new File(monthPath);
        if (!monthFolder.exists() && !monthFolder.isDirectory()) {
            monthFolder.mkdirs();
        }


        String dayPath = monthPath + "/" + day;
        File dayFolder = new File(dayPath);
        if (!dayFolder.exists() && !dayFolder.isDirectory()) {
            dayFolder.mkdirs();
        }


        return dayPath;
    }



    public static WorkbookDesigner testDbDataToExcelWithMulTables(Map<String, ResultSet> map, String templateExcelFilePath, String jobNameDayPath,ExcelModel excelModel) {
        // 验证License
        if (!getLicense()) {
            return null;
        }
        Workbook wb = null;
        WorkbookDesigner designer = new WorkbookDesigner();
        try {
            wb = new Workbook(templateExcelFilePath);
            designer.setWorkbook(wb);
//            for (Map.Entry<String, ResultSet> rs : map.entrySet()) {
//                designer.setDataSource(rs.getKey(), rs.getValue());
//            }
            //List<List<>>
            //designer.setDataSource("Person",getPersons());
            designer.process();
            designer.getWorkbook().calculateFormula();
            String newFileName = DateUtils.timestampToDataString(System.currentTimeMillis()) + ".xlsx";
            designer.getWorkbook().save(newFileName, SaveFormat.XLSX);
            excelModel.setExcelPath(newFileName);
            return designer;
        } catch (Exception e) {
            LOG.info("数据灌入Excel失败:"+e.getMessage());  //To change body of catch statement use File | Settings | File Templates.
            return null;
        }
    }

}
